using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Common;
using Garlic.CC.Utility;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

using Garlic.DM.DataModel;

namespace Garlic.DAL
{
	/// <summary>
	/// DataAccess
	/// </summary>
	public partial class UserDAL
	{
		#region Sql const
		public  const string TS_BAS_USER_SELECT_BY_ID=" SELECT employee_name,status,company,department,office_phone,mobile_phone,email,password,shift,password1,password2,password3,password_expire_time,user_id,user_name,user_type,create_user,create_time,modify_user,modify_time,fail_login"+
											" FROM TS_BAS_User"+
											" WHERE 1=1 " +
											" and user_id = :user_id";

											
		public  const string TS_BAS_USER_SELECT=" SELECT "+
											"employee_name,"+
											"status,"+
											"company,"+
											"department,"+
											"office_phone,"+
											"mobile_phone,"+
											"email,"+
											"password,"+
											"shift,"+
											"password1,"+
											"password2,"+
											"password3,"+
											"password_expire_time,"+
											"user_id,"+
											"user_name,"+
											"user_type,"+
											"create_user,"+
											"create_time,"+
											"modify_user,"+
											"modify_time,"+
											"fail_login"+
											" FROM TS_BAS_User {0}";
														
								
	
		public  const string TS_BAS_USER_INSERT=" INSERT INTO TS_BAS_User ("+
											"employee_name,"+
											"status,"+
											"company,"+
											"department,"+
											"office_phone,"+
											"mobile_phone,"+
											"email,"+
											"password,"+
											"shift,"+
											"password1,"+
											"password2,"+
											"password3,"+
											"password_expire_time,"+
											"user_id,"+
											"user_name,"+
                                            "user_type," +
                                            "create_user," +
                                            "create_time," +
											"fail_login"+
										") VALUES ("+
											":employee_name,"+
											":status,"+
											":company,"+
											":department,"+
											":office_phone,"+
											":mobile_phone,"+
											":email,"+
											":password,"+
											":shift,"+
											":password1,"+
											":password2,"+
											":password3,"+
											":password_expire_time,"+
											":user_id,"+
											":user_name,"+
                                            ":user_type," +
                                            ":create_user," +
                                            ":create_time," +
											":fail_login"+
										")";
									
									
		public  const string TS_BAS_USER_UPDATE=" UPDATE TS_BAS_User SET "+
											" employee_name = :employee_name, "+
											" status = :status, "+
											" company = :company, "+
											" department = :department, "+
											" office_phone = :office_phone, "+
											" mobile_phone = :mobile_phone, "+
											" email = :email, "+
											" password = :password, "+
											" shift = :shift, "+
											" password1 = :password1, "+
											" password2 = :password2, "+
											" password3 = :password3, "+
											" password_expire_time = :password_expire_time, "+
											" user_name = :user_name, "+
											" user_type = :user_type, "+
											" create_user = :create_user, "+
											" create_time = :create_time, "+
                                            " modify_user = :modify_user, " +
                                            " modify_time = :modify_time, " +
											" fail_login = :fail_login "+
										" WHERE 1=1 " +
											" and user_id = :user_id";
										
	
	
		public  const string TS_BAS_USER_DELETE=" DELETE FROM TS_BAS_User" +
										" WHERE 1=1"+
											" and user_id = :user_id";
										
										
		
		#endregion
		
		/// <summary>
		/// Get UserInfo by user_id
		/// </summary>
		/// <param name="user_id">UserInfo </param>
		/// <returns></returns>
		public static UserInfo GetInfo(int user_id)
		{
	
				Database db = DatabaseFactory.CreateDatabase();
				DbCommand dbCommand = db.GetSqlStringCommand(TS_BAS_USER_SELECT_BY_ID);
			db.AddInParameter(dbCommand, "user_id", DbType.Int32,user_id);
				

				using (IDataReader dr = db.ExecuteReader(dbCommand))
				{
					if (dr.Read())
					{
						return CreateUserInfo(dr);
					}
					else
						return null;
				}
		}
	
	
		/// <summary>
		/// Get data list
		/// </summary>
		/// <param name="textWhere">conditon</param>
		/// <param name="orderText">sort</param>
		/// <returns>UserInfo list </returns>
		public static List<UserInfo> GetList(string textWhere,string orderText)
		{
			string query=string.Empty;
			if(!string.IsNullOrEmpty(textWhere))
			{
			query+=" where "+textWhere; 
			}
			
			if(!string.IsNullOrEmpty(orderText))
			{
			query+=" order by "+orderText;
			}
			
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(string.Format(TS_BAS_USER_SELECT,query));
			
			return GetList(string.Format(TS_BAS_USER_SELECT,query));
			
		}
		
		/// <summary>
		/// Get data list
		/// </summary>
		/// <param name="sql">SQL statement</param>
		/// <returns>UserInfo list </returns>
		public static List<UserInfo> GetList(string sql)
		{
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(sql);
			List<UserInfo> list=new List<UserInfo>();
			using (IDataReader dr = db.ExecuteReader(dbCommand))
			{
				while(dr.Read())
				{
					list.Add(CreateUserInfo(dr));
				}
			}
			
			return list;
		}

	
		/// <summary>
		/// Add
		/// </summary>
		/// <param name="userInfo"> entity</param>
		public static int Add(UserInfo userInfo)
		{

            string TS_BAS_USER_INSERT1=" INSERT INTO TS_BAS_User ("+
											"employee_name,"+
											"status,"+
                                            "company," +
                                            "department," +
                                            "office_phone," +
                                            "mobile_phone," +
                                            "email," +
                                            "password," +
                                            "shift," +
                                            "password1," +
                                            "password2," +
                                            "password3," +
                                            "password_expire_time," +
											"user_id,"+
											"fail_login"+
										") VALUES ("+
											":employee_name,"+
											":status,"+
                                            ":company," +
                                            ":department," +
                                            ":office_phone," +
                                            ":mobile_phone," +
                                            ":email," +
                                            ":password," +
                                            ":shift," +
                                            ":password1," +
                                            ":password2," +
                                            ":password3," +
                                            ":password_expire_time," +
											":user_id,"+
											":fail_login"+
										")";
				Database db = DatabaseFactory.CreateDatabase();
	
				DbCommand dbCommand = db.GetSqlStringCommand(TS_BAS_USER_INSERT);
				
				db.AddInParameter(dbCommand, "employee_name",DbType.AnsiString,userInfo.Employee_name); 
				db.AddInParameter(dbCommand, "status",DbType.Int32,userInfo.Status); 
                db.AddInParameter(dbCommand, "company",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Company)); 
                db.AddInParameter(dbCommand, "department",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Department)); 
                db.AddInParameter(dbCommand, "office_phone",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Office_phone));
                db.AddInParameter(dbCommand, "mobile_phone", DbType.AnsiString, DBConvert.ToDBValue(userInfo.Mobile_phone));
                db.AddInParameter(dbCommand, "email", DbType.AnsiString, DBConvert.ToDBValue(userInfo.Email));
                db.AddInParameter(dbCommand, "password", DbType.AnsiString, DBConvert.ToDBValue(userInfo.Password));
                db.AddInParameter(dbCommand, "shift", DbType.Int32, DBConvert.ToDBValue(userInfo.Shift));
                db.AddInParameter(dbCommand, "password1", DbType.AnsiString, DBConvert.ToDBValue(userInfo.Password1));
                db.AddInParameter(dbCommand, "password2", DbType.AnsiString, DBConvert.ToDBValue(userInfo.Password2));
                db.AddInParameter(dbCommand, "password3", DbType.AnsiString, DBConvert.ToDBValue(userInfo.Password3));
                db.AddInParameter(dbCommand, "password_expire_time", DbType.DateTime, userInfo.Password_expire_time);
                db.AddInParameter(dbCommand, "password_expire_time", DbType.DateTime, DateTime.MinValue); 
				db.AddInParameter(dbCommand, "user_id",DbType.Int32,userInfo.User_id);
                db.AddInParameter(dbCommand, "user_name", DbType.AnsiString, DBConvert.ToDBValue(userInfo.User_name));
                db.AddInParameter(dbCommand, "user_type", DbType.Int32, DBConvert.ToDBValue(userInfo.User_type));
                db.AddInParameter(dbCommand, "create_user", DbType.Int32, DBConvert.ToDBValue(userInfo.Create_user));
                db.AddInParameter(dbCommand, "create_time", DbType.DateTime, userInfo.Create_time); 
				db.AddInParameter(dbCommand, "fail_login",DbType.Int32,userInfo.Fail_login); 
				
            db.ExecuteNonQuery(dbCommand);
				return 0;
		}
		
		/// <summary>
		/// Update
		/// </summary>
		/// <param name="userInfo"> utility</param>
		public static void Update(UserInfo userInfo)
		{
			Database db = DatabaseFactory.CreateDatabase();
	
				DbCommand dbCommand = db.GetSqlStringCommand(TS_BAS_USER_UPDATE);
				
				db.AddInParameter(dbCommand, "employee_name",DbType.AnsiString,userInfo.Employee_name); 
				db.AddInParameter(dbCommand, "status",DbType.Int32,userInfo.Status); 
				db.AddInParameter(dbCommand, "company",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Company)); 
				db.AddInParameter(dbCommand, "department",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Department)); 
				db.AddInParameter(dbCommand, "office_phone",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Office_phone)); 
				db.AddInParameter(dbCommand, "mobile_phone",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Mobile_phone)); 
				db.AddInParameter(dbCommand, "email",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Email)); 
				db.AddInParameter(dbCommand, "password",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Password)); 
				db.AddInParameter(dbCommand, "shift",DbType.Int32,DBConvert.ToDBValue(userInfo.Shift)); 
				db.AddInParameter(dbCommand, "password1",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Password1)); 
				db.AddInParameter(dbCommand, "password2",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Password2)); 
				db.AddInParameter(dbCommand, "password3",DbType.AnsiString,DBConvert.ToDBValue(userInfo.Password3)); 
				db.AddInParameter(dbCommand, "password_expire_time",DbType.DateTime,userInfo.Password_expire_time); 
				db.AddInParameter(dbCommand, "user_id",DbType.Int32,userInfo.User_id); 
				db.AddInParameter(dbCommand, "user_name",DbType.AnsiString,DBConvert.ToDBValue(userInfo.User_name)); 
				db.AddInParameter(dbCommand, "user_type",DbType.Int32,DBConvert.ToDBValue(userInfo.User_type)); 
				db.AddInParameter(dbCommand, "create_user",DbType.Int32,DBConvert.ToDBValue(userInfo.Create_user)); 
				db.AddInParameter(dbCommand, "create_time",DbType.DateTime,DBConvert.ToDBValue(userInfo.Create_time)); 
				db.AddInParameter(dbCommand, "modify_user",DbType.Int32,DBConvert.ToDBValue(userInfo.Modify_user)); 
				db.AddInParameter(dbCommand, "modify_time",DbType.DateTime,DBConvert.ToDBValue(userInfo.Modify_time)); 
				db.AddInParameter(dbCommand, "fail_login",DbType.Int32,userInfo.Fail_login); 
				
				db.ExecuteNonQuery(dbCommand);
		}
		
		/// <summary>
		/// Delete
		/// </summary>
		/// <param name="user_id">TS_BAS_User Number</param>
		/// <returns></returns>
		public static void Delete(int user_id)
		{
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(TS_BAS_USER_DELETE);
			db.AddInParameter(dbCommand, "user_id", DbType.Int32,user_id);
			db.ExecuteNonQuery(dbCommand);
		}

        
		
	

	
		private static UserInfo CreateUserInfo(IDataReader rdr)
		{
			UserInfo myuserInfo = new UserInfo();
			myuserInfo.Employee_name=rdr.GetString(0);
			myuserInfo.Status=rdr.GetInt32(1);
			myuserInfo.Company=DBConvert.GetString(rdr,2);
			myuserInfo.Department=DBConvert.GetString(rdr,3);
			myuserInfo.Office_phone=DBConvert.GetString(rdr,4);
			myuserInfo.Mobile_phone=DBConvert.GetString(rdr,5);
			myuserInfo.Email=DBConvert.GetString(rdr,6);
			myuserInfo.Password=DBConvert.GetString(rdr,7);
			myuserInfo.Shift=DBConvert.GetInt32(rdr,8);
			myuserInfo.Password1=DBConvert.GetString(rdr,9);
			myuserInfo.Password2=DBConvert.GetString(rdr,10);
			myuserInfo.Password3=DBConvert.GetString(rdr,11);
			myuserInfo.Password_expire_time=rdr.GetDateTime(12);
			myuserInfo.User_id=rdr.GetInt32(13);
			myuserInfo.User_name=DBConvert.GetString(rdr,14);
			myuserInfo.User_type=DBConvert.GetInt32(rdr,15);
			myuserInfo.Create_user=DBConvert.GetInt32(rdr,16);
			myuserInfo.Create_time=DBConvert.GetDateTime(rdr,17);
			myuserInfo.Modify_user=DBConvert.GetInt32(rdr,18);
			myuserInfo.Modify_time=DBConvert.GetDateTime(rdr,19);
			myuserInfo.Fail_login=rdr.GetInt32(20);

			return myuserInfo;
		}
	}
}
